/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package khmhappointmentsystem;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author mbelisle
 */
public class Clinic {

    static final Logger logger = Logger.getLogger(Clinic.class.getName());

    public static String getClinicID(String name, String specialist) {
        String id = "";
        try {
            String query = "Select clinicID FROM clinic WHERE name = ? AND specialist = ?";
            PreparedStatement pstmt = Environment.getDbConn().prepareStatement(query);
            pstmt.setString(1, name);
            pstmt.setString(2, specialist);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                id = rs.getString("clinicID");
            }
            rs.close();
            pstmt.close();
        } catch (Exception e) {
            String message = "Error occured while retrieving clinicID.";
            logger.log(Level.SEVERE, message, e);
        }
        return id;
    }

    public static boolean verifyAppointment(String specialist, String clinic, String date) {
        boolean exist = false;
        try {
            String query = "Select pt_id FROM Appointments WHERE specialist = ? AND clinic = ?, AND date = ?";
            PreparedStatement pstmt = Environment.getDbConn().prepareStatement(query);
            pstmt.setString(1, specialist);
            pstmt.setString(2, clinic);
            pstmt.setString(3, date);
            pstmt.executeQuery();
            exist = true;
            pstmt.close();
        } catch (Exception e) {
            String message = "Error occured while retrieving clinicID.";
            logger.log(Level.SEVERE, message, e);
        }
        return exist;
    }

    public static int maxVisitors(String clinicID) {
        int total = 0;
        try {
            String sql = "Select limit FROM clinic WHERE clinicID = ?";
            PreparedStatement pstmt = Environment.getDbConn().prepareStatement(sql);
            pstmt.setString(1, clinicID);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                total = rs.getInt("limit");
            }
            rs.close();
            pstmt.close();
        } catch (Exception e) {
            String message = "An error occured while retrieving the max visitors.";
            logger.log(Level.SEVERE, message, e);
        }
        return total;
    }

    public static int totalVisitors(String specialist, String date) {
        int currTotal = 0;
        try {
            String sql = "Select Count(*) as Total FROM Appointments WHERE specialist LIKE ? and date like ?";
            PreparedStatement pstmt = Environment.getDbConn().prepareStatement(sql);
            pstmt.setString(1, specialist);
            pstmt.setString(2, date);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                currTotal = rs.getInt("Total");
            }
            rs.close();
            pstmt.close();
        } catch (Exception e) {
            String message = "Error occured while counting total appointments.";
            logger.log(Level.SEVERE, message, e);
        }
        return currTotal;
    }

    public static ArrayList<String> getUser() {
        ArrayList<String> groups = new ArrayList<String>();
        String sql = "SELECT DISTINCT therapist FROM therapist ORDER BY therapist ASC";
        try {
            PreparedStatement prep = Environment.getDbConn().prepareStatement(sql);
            ResultSet rs = prep.executeQuery();
            while (rs.next()) {
                groups.add(rs.getString("therapist"));
            }
            rs.close();
            prep.close();
        } catch (Exception e) {
            String message = "An error occurred while getting the therapist list.";
            logger.log(Level.SEVERE, message, e);
        }
        return groups;
    }

    public static boolean CheckinAppointments(String idnumber, String ptclinic, String name, String invoice, String diagnosis, String treatment, String clinicdate) {
        boolean successful = false;
        try {
            String query = "Insert Into PhysiotherapyApp ("
                    + "pt_id,"
                    + "clinic,"
                    + "therapist,"
                    + "invoice,"
                    + "diagnosis,"
                    + "treatment,"
                    + "date)"
                    + "Values(?,?,?,?,?,?,?)";

            PreparedStatement pstmt = Environment.getDbConn().prepareStatement(query);
            pstmt.setString(1, idnumber);
            pstmt.setString(2, ptclinic);
            pstmt.setString(3, name);
            pstmt.setString(4, invoice);
            pstmt.setString(5, diagnosis);
            pstmt.setString(6, treatment);
            pstmt.setString(7, clinicdate);
            pstmt.executeUpdate();
            successful = true;
        } catch (Exception e) {
            String message = "An error occurred while checking in the patient.";
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, message, e);
            successful = false;
        }
        return successful;
    }
}
